{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据已保存到 20240522.csv 和数据库 notifications.db 中。\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "import re\n",
    "from datetime import datetime\n",
    "\n",
    "# 配置URL和日期\n",
    "url = 'https://www.dufe.edu.cn/r_6.html'\n",
    "today = datetime.today().strftime('%Y%m%d')\n",
    "csv_filename = f'{today}.csv'\n",
    "\n",
    "# 请求网页内容\n",
    "response = requests.get(url)\n",
    "response.encoding = 'utf-8'\n",
    "soup = BeautifulSoup(response.text, 'html.parser')\n",
    "\n",
    "# 提取数据\n",
    "data = []\n",
    "\n",
    "for item in soup.select('.xw li'):\n",
    "    project_name_tag = item.find('a', class_='color-3')\n",
    "    if not project_name_tag:\n",
    "        continue\n",
    "    project_name = project_name_tag.text.strip()\n",
    "    project_url = 'https://www.dufe.edu.cn/' + project_name_tag['href']\n",
    "    \n",
    "    # 过滤掉含有\"研讨会\"、\"实践\"关键字的新闻记录\n",
    "    if any(keyword in project_name for keyword in [\"研讨会\", \"实践\"]):\n",
    "        continue\n",
    "\n",
    "    # 去掉“关于组织、关于、工作的通知、的通知”这样的文字\n",
    "    project_name = re.sub(r'关于组织|关于|工作的通知|的通知', '', project_name)\n",
    "    \n",
    "    # 发布日期\n",
    "    publish_date_tag = item.find_next('div', class_='color-6')\n",
    "    publish_date = publish_date_tag.text.strip() if publish_date_tag else ''\n",
    "    \n",
    "    # 请求项目详情页\n",
    "    detail_response = requests.get(project_url)\n",
    "    detail_response.encoding = 'utf-8'\n",
    "    detail_soup = BeautifulSoup(detail_response.text, 'html.parser')\n",
    "    \n",
    "    # 截止日期\n",
    "    deadline_date = ''\n",
    "    deadline_tag = detail_soup.find(text=re.compile(r'截止时间：'))\n",
    "    if deadline_tag:\n",
    "        deadline_date = re.search(r'\\d{4}年\\d{1,2}月\\d{1,2}日', deadline_tag).group()\n",
    "    \n",
    "    # 联系人\n",
    "    contact_person = ''\n",
    "    contact_tag = detail_soup.find(text=re.compile(r'联系人：'))\n",
    "    if contact_tag:\n",
    "        contact_person = contact_tag.split('：')[1].strip()\n",
    "\n",
    "    # 项目类型\n",
    "    project_type = '其他'\n",
    "    if '课题' in project_name:\n",
    "        project_type = '课题申报'\n",
    "    elif '结题' in project_name:\n",
    "        project_type = '项目结题'\n",
    "    elif '成果认定' in project_name:\n",
    "        project_type = '成果认定'\n",
    "    elif '成果评级' in project_name:\n",
    "        project_type = '成果评级'\n",
    "\n",
    "    data.append([project_name, project_type, publish_date, deadline_date, contact_person, project_url])\n",
    "\n",
    "# 保存到CSV文件\n",
    "df = pd.DataFrame(data, columns=['项目名称', '项目类型', '发布日期', '截止日期', '联系人', '网页URL'])\n",
    "df.to_csv(csv_filename, index=False, encoding='utf-8-sig')\n",
    "\n",
    "# 保存到数据库\n",
    "conn = sqlite3.connect('notifications.db')\n",
    "df.to_sql('notifications', conn, if_exists='append', index=False)\n",
    "conn.close()\n",
    "\n",
    "print(f'数据已保存到 {csv_filename} 和数据库 notifications.db 中。')\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "MyMLenvAllinOne",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
